package routePlanner.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import com.google.common.collect.Lists;
import routePlanner.entities.Atraction;
import routePlanner.entities.AtractionType;
import routePlanner.entities.City;
import routePlanner.entities.CityConnection;
import routePlanner.entities.Vehicle;

public class RouteDAO {
	private static final String CONNECTION_TABLE = "connections";
    private static final String CONNECTION_TABLE_ID = "id_connection";
    private static final String VEHICLES_TABLE = "vehicles";
    private static final String VEHICLES_TABLE_ID = "id_vehicle";
    private static final String VEHICLES_TABLE_NAME = "vehicle_name";
    private static final String CITIES_TABLE = "cities";
    private static final String CITIES_TABLE_ID = "id_city";
    private static final String CITIES_TABLE_NAME = "city_name";
    private static final String ATRACTIONS_TABLE = "atractions";
    private static final String ATRACTIONS_TABLE_ID = "id_atraction";
    private static final String ATRACTIONS_TABLE_NAME = "atraction_name";
    private RouteDatabaseCreator dbCreator;
    private Connection connection;

    public RouteDAO() {
            dbCreator = new RouteDatabaseCreator();
            connection = dbCreator.getConnection();
    }

    public void insertCityConnection(CityConnection cityConnection) {
            try {
                    PreparedStatement preparedStatement = connection
                                    .prepareStatement("insert into " + CONNECTION_TABLE
                                                    + " values (NULL, ?, ?, ?, ?);");
                    preparedStatement.setInt(1, cityConnection.getStartCity().getId());
                    preparedStatement.setInt(2, cityConnection.getEndCity().getId());
                    preparedStatement.setInt(3, cityConnection.getVehicle().getId());
                    preparedStatement.setDouble(4, cityConnection.getAvgSpeed());
                    preparedStatement.execute();
                    preparedStatement.close();
            } catch (SQLException ex) {
                    ex.printStackTrace();
            }
    }

    public void insertCity(City city) {
            try {
                    PreparedStatement preparedStatement = connection
                                    .prepareStatement("insert into " + CITIES_TABLE
                                                    + " values (NULL, ?, ?, ?, ?, ?);");
                    preparedStatement.setString(1, city.getName());
                    preparedStatement.setInt(2, city.getAtraction().getId());
                    preparedStatement.setInt(3, city.getLatitude());
                    preparedStatement.setInt(4, city.getLongitude());
                    preparedStatement.setInt(5, city.getAltitude());
                    preparedStatement.execute();
                    preparedStatement.close();
            } catch (SQLException ex) {
                    ex.printStackTrace();
            }
    }

    public void insertAtraction(Atraction atraction) {
            try {
                    PreparedStatement preparedStatement = connection
                                    .prepareStatement("insert into " + ATRACTIONS_TABLE
                                                    + " values (NULL, ?, ?, ?, ?);");
                    preparedStatement.setString(1, atraction.getName());
                    preparedStatement.setString(2, atraction.getDescription());
                    preparedStatement.setDouble(3, atraction.getRank());
                    preparedStatement.setString(4, atraction.getType().toString());
                    preparedStatement.execute();
                    preparedStatement.close();
            } catch (SQLException ex) {
                    ex.printStackTrace();
            }
    }

    public void insertVehicle(Vehicle vehicle) {
            try {
                    PreparedStatement preparedStatement = connection
                                    .prepareStatement("insert into " + VEHICLES_TABLE
                                                    + " values (NULL, ?, ?);");
                    preparedStatement.setString(1, vehicle.getName());
                    preparedStatement.setDouble(2, vehicle.getKmCost());
                    preparedStatement.execute();
                    preparedStatement.close();
            } catch (SQLException ex) {
                    ex.printStackTrace();
            }
    }

    public List<CityConnection> getCitiesConnections() {
            try {
                    return prepareCitiesConnections(getAllQuery(CONNECTION_TABLE));
            } catch (SQLException e) {
                    e.printStackTrace();
            }
            return null;
    }

    private List<CityConnection> prepareCitiesConnections(ResultSet results)
                    throws SQLException {
            List<CityConnection> citiesConnections = Lists.newArrayList();
            while (results.next()) {
                    citiesConnections.add(prepareCityConnection(results));
            }
            results.close();
            return citiesConnections;
    }

    public CityConnection getCityConnectionById(int id) {
            try {
                    ResultSet rs = prepareExecuteSelectQuery(CONNECTION_TABLE, CONNECTION_TABLE_ID, id);
                    CityConnection result = prepareCityConnection(rs);
                    rs.close();
                    return result;
            } catch (SQLException e) {
                    e.printStackTrace();
            }
            return null;
    }

    private CityConnection prepareCityConnection(ResultSet results)
                    throws SQLException {
            int id_connection = results.getInt("id_connection");
            City startCity = getCityById(results.getInt("start_City"));
            City end_city = getCityById(results.getInt("end_city"));
            Vehicle vehicle = getVehicleById(results.getInt("vehicle_id"));
            double avg_speed = results.getDouble("avg_speed");
            return new CityConnection(id_connection, startCity, end_city, vehicle,
                            avg_speed);
    }

    public List<Vehicle> getVehicles() {
            try {
                    return prepareVehicles(getAllQuery(VEHICLES_TABLE));
            } catch (SQLException e) {
                    e.printStackTrace();
            }
            return null;
    }

    private List<Vehicle> prepareVehicles(ResultSet results)
                    throws SQLException {
            List<Vehicle> vehicles = Lists.newArrayList();
            while (results.next()) {
                    vehicles.add(preapreVehicle(results));
            }
            results.close();
            return vehicles;
    }

    public Vehicle getVehicleById(int id) {
            try {
                    ResultSet results = prepareExecuteSelectQuery(VEHICLES_TABLE, VEHICLES_TABLE_ID, id);
                    Vehicle result = preapreVehicle(results);
                    results.close();
                    return result;
            } catch (SQLException e) {
                    e.printStackTrace();
            }
            return null;
    }
    
    public Vehicle getVehicleByName(String name) {
            try {
                    ResultSet results = prepareExecuteSelectQueryName(VEHICLES_TABLE, VEHICLES_TABLE_NAME, name);
                    Vehicle result = preapreVehicle(results);
                    results.close();
                    return result;
            } catch (SQLException e) {
                    e.printStackTrace();
            }
            return null;
    }

    private Vehicle preapreVehicle(ResultSet results) throws SQLException {
            int id_vehicle = results.getInt("id_vehicle");
            String vehicle_name = results.getString("vehicle_name");
            double km_cost = results.getDouble("km_cost");
            return new Vehicle(id_vehicle, vehicle_name, km_cost);
    }

    public City getCityById(int id) {
            try {
                    ResultSet results = prepareExecuteSelectQuery(CITIES_TABLE, CITIES_TABLE_ID, id);
                    City city = prepareCity(results);
                    results.close();
                    return city;
            } catch (SQLException e) {
                    e.printStackTrace();
            }
            return null;
    }

    public City getCityByName(String name) {
            try {
                    ResultSet results = prepareExecuteSelectQueryName(CITIES_TABLE, CITIES_TABLE_NAME, name);
                    City city = prepareCity(results);
                    results.close();
                    return city;
            } catch (SQLException e) {
                    e.printStackTrace();
            }
            return null;
    }
    
    public List<City> getCities() {
            try {
                    return prepareCities(getAllQuery(CITIES_TABLE));
            } catch (SQLException e) {
                    e.printStackTrace();
            }
            return null;
    }

    private List<City> prepareCities(ResultSet results) throws SQLException {
            List<City> cities = Lists.newArrayList();
            while (results.next()) {
                    cities.add(prepareCity(results));
            }
            results.close();
            return cities;
    }

    private City prepareCity(ResultSet results) throws SQLException {
            int city_id = results.getInt("id_city");
            String name = results.getString("city_name");
            Atraction atraction = getAtractionById(results.getInt("id_atraction"));
            int latitude = results.getInt("latitude");
            int longitude = results.getInt("longitude");
            int altitude = results.getInt("altitude");
            return new City(city_id, name, atraction, latitude, longitude, altitude);
    }

    public List<Atraction> getAtractions() {
            try {
                    return prepareAtractions(getAllQuery(ATRACTIONS_TABLE));
            } catch (SQLException e) {
                    e.printStackTrace();
            }
            return null;
    }

    private ResultSet getAllQuery(String tableName) throws SQLException {
            PreparedStatement st = connection.prepareStatement("SELECT * FROM "
                            + tableName);
            ResultSet results = st.executeQuery();
            results.next();
            return results;
    }

    private List<Atraction> prepareAtractions(ResultSet results)
                    throws SQLException {
            List<Atraction> atractions = Lists.newArrayList();
            while (results.next()) {
                    atractions.add(prepareAtraction(results));
            }
            results.close();
            return atractions;
    }

    public Atraction getAtractionById(int id) {
            try {
                    ResultSet rs = prepareExecuteSelectQuery(ATRACTIONS_TABLE, ATRACTIONS_TABLE_ID, id);
                    Atraction atraction = prepareAtraction(rs);
                    rs.close();
                    return atraction;
            } catch (SQLException e) {
                    e.printStackTrace();
            }
            return null;
    }

    public Atraction getAtractionByName(String name) {
            try {
                    ResultSet rs = prepareExecuteSelectQueryName(ATRACTIONS_TABLE, ATRACTIONS_TABLE_NAME, name);
                    Atraction atraction = prepareAtraction(rs);
                    rs.close();
                    return atraction;
            } catch (SQLException e) {
                    e.printStackTrace();
            }
            return null;
    }
    
    private ResultSet prepareExecuteSelectQuery(String tableName,
                    String id_row, int id) throws SQLException {
            PreparedStatement st = connection.prepareStatement("SELECT * FROM "
                            + tableName + " WHERE " + id_row + "='" + id + "';");
            ResultSet results = st.executeQuery();
            results.next();
            return results;
    }
    
    private ResultSet prepareExecuteSelectQueryName(String tableName,
                    String id_row, String name) throws SQLException {
            PreparedStatement st = connection.prepareStatement("SELECT * FROM "
                            + tableName + " WHERE " + id_row + "='" + name + "';");
            ResultSet results = st.executeQuery();
            results.next();
            return results;
    }

    private Atraction prepareAtraction(ResultSet results) throws SQLException {
            int id_atraction = results.getInt("id_atraction");
            String atraction_name = results.getString("atraction_name");
            String description = results.getString("description");
            Double rank = results.getDouble("rank");
            AtractionType atractionType = AtractionType.getByName(results
                            .getString("atractionType"));
            return new Atraction(id_atraction, atraction_name, description, rank,
                            atractionType);
    }
}
